/**
 * 方式二： 先把数据全部写入categoryOption，成功后，遍历categoryOption，创建relation表
 */
const fs = require('fs');
const moment = require('moment');
const Excel = require('exceljs');
const path = require('path');
const async = require('async');
const Promise = require('bluebird');
let models;

try {
	let stats = fs.statSync('./dist');
} catch (e) {
	fs.mkdirSync('./dist');
}

function copyDb(cb) {
	let rs = fs.createReadStream('./asset/blank.db');
	let newPath = path.join(__dirname, `dist/${moment().format('YYYY-MM-DD')}.db`);
	let ws = fs.createWriteStream(newPath);
	rs.on('error', function(err) {
		cb(err);
	});
	ws.on('error', function(err) {
		cb(err);
	});
	ws.on('finish', function() {
		cb(null, newPath);
	})
	rs.pipe(ws);
}

function initDb(path, cb) {
	log(`成功复制db: ${path}`)
	models = require('./model')(path);
	models.sequelize.sync({
		force: true
	}).then(() => {
		log(`成功初始化db...`)
		cb(null);
	}, err => cb(err));
}

function parseProductExcel(cb) {
	let workbook = new Excel.Workbook();
	workbook.xlsx.readFile('./asset/产品详情表.xlsx')
		.then(() => {
			let worksheet = workbook.getWorksheet('产品大类');
			let types = [];
			worksheet.eachRow(function(row, rowNumber) {
				if (rowNumber === 1) return;
				types.push({
					_id: row.getCell(1).value,
					desc: row.getCell(2).value,
					img: row.getCell(3).value
				});
			});
			return models.Category.bulkCreate(types).then(() => log(`产品类别表导入${types.length}条记录`));
		}).then(() => {
			let worksheet = workbook.getWorksheet('产品详情');
			let products = [];
			worksheet.eachRow(function(row, rowNumber) {
				if (rowNumber === 1) return;
				products.push({
					name: filterStr(row.getCell(2).value),
					thumbnail: filterStr(row.getCell(3).value),
					conditionLive: filterStr(row.getCell(4).value),
					conditionWork: filterStr(row.getCell(5).value),
					conditionShop: filterStr(row.getCell(6).value),
					poster: row.getCell(7).value,
					desc: filterStr(row.getCell(8).value),
					detail: filterStr(row.getCell(9).value),
					bonus: row.getCell(10).value,
					categoryId: row.getCell(1).value
				});
			});
			return models.Product.bulkCreate(products).then(() => log(`产品详情表导入${products.length}条记录`));
		})
		.then(() => cb(null))
		.catch(err => cb(err));
}
function filterStr (str) {
	return unescape(escape(str));
}
function importBonusOption(environment, workbook) {
	let data = [];
	for (let v of['位置', '环境', '物业', '门禁', '楼层']) {
		data = data.concat(readBonusSheet(v, environment, workbook.getWorksheet(v)));
	}
	return models.BonusOption.bulkCreate(data).then(() => log(`bonusOption导入${data.length}条记录`));
}

function importCategoryOption(environment, workbook) {
	let data = [];
	for (let v of['建面', '户型', '阳台']) {
		data = data.concat(readDeviceNumSheet(v, environment, workbook.getWorksheet(v)));
	}
	return models.CategoryOption.bulkCreate(data).then(() => log(`categoryOption导入${data.length}条记录`));
}

function parseConditionExcel(filePath, environment, cb) {
	let workbook = new Excel.Workbook();
	workbook.xlsx.readFile(filePath)
		.then(() => {
			return Promise.all([importBonusOption(environment, workbook), importCategoryOption(environment, workbook)])
		})
		.then(() => cb(null))
		.catch(err => cb(err))
}
//读取categoryOption,relate添加数据
//如果数据量很大可以考虑分批次插入
function processNumRelation(cb) {
	models.CategoryOption.findAll().then(categoryOptions => {
			let totalData = [];
			for (let obj of categoryOptions) {
				if (obj.cameraNum) {
					totalData.push({
						num: obj.cameraNum,
						categoryId: 1,
						optionId: obj._id
					});
				}
				if (obj.lockNum) {
					totalData.push({
						num: obj.lockNum,
						categoryId: 2,
						optionId: obj._id
					});
				}
				if (obj.detectNum) {
					totalData.push({
						num: obj.detectNum,
						categoryId: 3,
						optionId: obj._id
					});
				}
				if (obj.warnNum) {
					totalData.push({
						num: obj.warnNum,
						categoryId: 4,
						optionId: obj._id
					});
				}
				if (obj.handNum) {
					totalData.push({
						num: obj.handNum,
						categoryId: 5,
						optionId: obj._id
					});
				}
			}
			return models.CategoryRelateOption.bulkCreate(totalData).then(() => log(`categoryRelateOption导入${totalData.length}条记录`));
		})
		.then(() => cb(null))
		.catch(err => cb(err))
}

//复制一张categoryOption表，不需要多余字段
function alterCategoryOptionTable(cb) {
	models.sequelize.query('DROP TABLE IF EXISTS categoryOption').then(() => log('categoryOption删除成功'))
		.then(() => {
			return models.sequelize.query('ALTER TABLE categoryOptionSpread rename to categoryOption').then(() => log('重命名categoryOptionSpread成功'));
		})
		.then(() => cb(null)).catch(err => cb(err));
}

const conditionExcels = [];
conditionExcels.push({
	filePath: path.join(__dirname, 'asset/居住数据表.xlsx'),
	environment: '居住'
});
conditionExcels.push({
	filePath: path.join(__dirname, 'asset/办公数据表.xlsx'),
	environment: '办公'
});
conditionExcels.push({
	filePath: path.join(__dirname, 'asset/经营数据表.xlsx'),
	environment: '经营'
});
async.auto({
	task1: copyDb,
	task2: ['task1', (data, cb) => initDb(data.task1, cb)],
	task3: ['task2', (data, cb) => parseProductExcel(cb)],
	task4: ['task2', (data, cb) => {
		async.eachLimit(conditionExcels, 2, (obj, cb) => {
			parseConditionExcel(obj.filePath, obj.environment, cb);
		}, (err, result) => {
			if (err) return cb(err);
			cb(null);
		});
	}],
	task5: ['task3', 'task4', (data, cb) => {
		models.sequelize.query('INSERT INTO categoryOptionSpread(_id,type,environment,desc,img) SELECT _id,type,environment,desc,img FROM categoryOption')
			.then(() => log('categoryOptionSpread复制成功'))
			.then(() => cb(null))
			.catch(err => cb(err))
	}],
	task6: ['task5', (data, cb) => processNumRelation(cb)],
	task7: ['task6', (data, cb) => alterCategoryOptionTable(cb)]
}, (err, result) => {
	if (err) {
		log(err)
		return
	}
	log('导表结束...')
});

/**
 * 解析计分的sheet
 * @param  {[type]} type        [description]
 * @param  {[type]} environment [description]
 * @param  {[type]} worksheet   [description]
 * @return {[type]}             [description]
 */
function readBonusSheet(type, environment, worksheet) {
	let bonusOptions = [];
	worksheet.eachRow((row, rowNumber) => {
		if (rowNumber === 1) return;
		bonusOptions.push({
			type: type,
			environment: environment,
			desc: row.getCell(2).value,
			img: row.getCell(3).value,
			optionBonus: row.getCell(4).value
		});
	});
	return bonusOptions;
};

function readDeviceNumSheet(type, environment, worksheet) {
	let data = [];
	worksheet.eachRow((row, rowNumber) => {
		if (rowNumber === 1) return;
		data.push({
			type: type,
			environment: environment,
			desc: row.getCell(2).value,
			img: row.getCell(3).value,
			cameraNum: row.getCell(4).value,
			lockNum: row.getCell(5).value,
			detectNum: row.getCell(6).value,
			warnNum: row.getCell(7).value,
			handNum: row.getCell(8).value
		});
	});
	return data;
};

function log(msg) {
	console.log(msg)
}